﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageTrainingRegister_SearchVolunteerControl

    Public Sub New()

    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal GroupId As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal TrainingBHour As Integer, ByVal TrainingSHour As Integer) As DataTable

        Dim dat As New clsData

        Dim sql As New MyStringBuilder

        sql.AppendFormat("SELECT distinct A.VolunteerID,A.VolunteerName,vs.TrainingBHour,vs.TrainingSHour,CASE WHEN B.VolunteerID is null THEN '1' ELSE '0' END as IsEnable ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("LEFT JOIN (SELECT VolunteerID FROM TrainingRegister WHERE GroupId = {0}) B on A.VolunteerID = B.VolunteerID ", GroupId)
        sql.AppendFormat("inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")

        sql.AppendFormat("WHERE A.Active IN ('2','Y') ")
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0} ) ", VOrganizationID)
        End If
        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        'sql.AppendFormat("AND (A.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0}  ) or '{0}' =0) ", VOrganizationID)
        'sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        If VolunteerId <> "" Then
            sql.AppendFormat("AND (A.VolunteerId = '{0}' ) ", VolunteerId)
        End If

        If VolunteerName <> "" Then
            sql.AppendFormat("AND (A.VolunteerName like '%{0}%' ) ", VolunteerName)
        End If

        If TEL <> "" Then
            sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' ) or (A.OfficePhone like '%{0}%'))) ", TEL)

        End If

        If TrainingBHour <> "0" Then
            sql.AppendFormat("AND (vs.TrainingBHour < {0} ) ", TrainingBHour)
        End If

        If TrainingSHour <> "0" Then
            sql.AppendFormat("AND (vs.TrainingSHour < {0} ) ", TrainingSHour)

        End If
        sql.AppendFormat("ORDER BY A.VolunteerID asc")

        Return dat.GetData(sql)

    End Function

End Class
